from django.http import HttpResponse
from django.shortcuts import render
from rest_framework.decorators import api_view
from ims.method import *
from ims.models import *
import datetime
import openpyxl
import xlrd
import xlwt
from thdata.sql import conn_ims
from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
from django.db.models import Q
import sys

# Create your views here.



@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def design_msgs(request):
    if request.method == "POST":
        content = {"payload": [], "status": 0, "errmsg": ''}
        data = request.data
        now = datetime.datetime.now()
        msg = TbDesignmessage()
        try:
            t_ver = set(TbDesignmessage.objects.filter(t_name=data["t_name"]).values_list("t_ver", flat=1))
            if data["t_ver"] in t_ver:
                content["status"] = 1
                content["errmsg"] = "刀具名稱和版次已存在！"
                return content
            msg.t_name = data["t_name"]
            msg.t_ver = data["t_ver"]
            msg.t_type = data["t_type"]
            msg.t_spec = data["t_spec"]
            msg.t_diam = data["t_diam"]
            msg.t_flen = data["t_flen"]
            msg.t_fnum = data["t_fnum"]
            msg.t_mmat = data["t_mmat"]
            msg.desi_id = request.user
            msg.desi_time = now
            msg.save()
            content["payload"] = {"toolt_id": msg.id, "t_name": msg.t_name, "t_ver": msg.t_ver, "t_type": msg.t_type,
                                  "t_spec": msg.t_spec, "t_diam": msg.t_diam, "t_flen": msg.t_flen,
                                  "t_fnum": msg.t_fnum,
                                  "t_mmat": msg.t_mmat}
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return Response(content)
    if request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": None}
        t_name = request.GET.get("t_name", None)
        page_num = request.GET.get("page", None)
        search = request.GET.get('search', None)
        if t_name:
            result = TbDesignmessage.objects.filter(t_name=t_name).order_by("-id").values()
        else:
            result = TbDesignmessage.objects.order_by("-id").values()
        if search:
            search = str.lower(search)
            _result = []
            for i in result:
                if search in str.lower(i['t_name']) or search in str.lower(i['t_type']) or search in str.lower(
                        i['t_spec']):
                    _result.append(i)
            result = _result
        if page_num:
            pag_class = Paginator(result, 20)
        else:
            pag_class = Paginator(result, len(result) + 1)
            page_num = 1
        select_data = pag_class.page(page_num)
        content["payload"] = {"data": [], "page": {}}
        if select_data:
            for i in select_data:
                i["desi_time"] = i["desi_time"].strftime("%Y-%m-%d %H:%M:%S")
                content["payload"]["data"].append(i)
        content["payload"]["page"] = {'count': pag_class.count, 'page': int(page_num),
                                      'page_number': pag_class.page_range[-1]}
        return Response(content)

    if request.method == 'PUT':
        now = datetime.datetime.now()
        content = {"payload": [], "status": 0, "errmsg": ''}
        data = request.data
        try:
            if data:
                t_msg = TbDesignmessage.objects.filter(id=data['id'])
                t_msg.update(t_diam=data["t_diam"],
                             t_flen=data["t_flen"],
                             t_fnum=data["t_fnum"],
                             t_mmat=data["t_mmat"],
                             desi_id=str(request.user), desi_time=now)
                content['payload'].append("OK")
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    elif request.method == "DELETE":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            data = request.data
            tb_pd = TbProductHead.objects.filter(toolt=data['id']).values()
            if tb_pd:
                raise Exception("此刀具已有補正記錄，無法刪除")
            TbDesignmessage.objects.filter(id=data['id']).delete()
            content['payload'].append("OK")
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET"])
def design_msg(request, pk):
    if request.method == "GET":
        try:
            content = {"payload": {}, "status": 0, "errmsg": None}
            result = TbDesignmessage.objects.filter(id=pk).values()[0]
            content["payload"]["design_msg"] = result
            content["payload"]["wheel_msg"] = []
            content["payload"]["size_msg"] = []
            wheel_msg = TbWheelmessage.objects.filter(t_name=result["t_name"], t_ver=result["t_ver"]).order_by('m_type',
                                                                                                               'w_no').values()
            if wheel_msg:
                for i in wheel_msg:
                    content["payload"]["wheel_msg"].append(i)
            size_msg = TbDesignsize.objects.filter(t_name=result["t_name"], t_ver=result["t_ver"]).values().order_by('desc1')
            if size_msg:
                for i in size_msg:
                    content["payload"]["size_msg"].append(i)
            else:
                cutter_id = TbCTypeHead.objects.filter(c_type=result["t_type"]).values('id')
                if cutter_id:
                    result1 = TbCTypeBody.objects.filter(c_type_id=cutter_id[0]['id']).values()
                    for i in result1:
                        size_cot = {}
                        size_cot['t_name'] = result["t_name"]
                        size_cot['t_ver'] = result["t_ver"]
                        size_cot['d_code'] = i['size_code']
                        size_cot['grindingsite'] = i['size_name']
                        size_cot['g_type'] = i['g_type']
                        size_cot['status'] = i['status']
                        size_cot['context'] = i['context']
                        size_cot['desc1'] = i['desc1']
                        content["payload"]["size_msg"].append(size_cot)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def wheel(request, pk):
    if request.method == "POST":
        content = {"payload": [], "status": 0, "errmsg": ''}
        data = request.data
        try:
            now = datetime.datetime.now()
            t_msg = TbDesignmessage.objects.filter(id=pk).values('t_name', 't_ver')
            if t_msg:
                w_msg = TbWheelmessage()
                w_msg.t_name = t_msg[0]['t_name']
                w_msg.t_ver = t_msg[0]['t_ver']
                w_msg.w_no = data["w_no"]  # 砂輪序號
                w_msg.w_type = data["w_type"]  # 砂輪型號
                w_msg.w_feed = data["w_feed"]  # 砂輪粒度
                w_msg.w_speed = data["w_speed"]  # 砂輪轉速
                w_msg.w_gran = data["w_gran"]  # 砂輪進給
                w_msg.m_type = data["m_type"]  # 機台型號
                w_msg.toolt_id = pk
                w_msg.desi_id = request.user
                w_msg.desi_time = now
                w_msg.save()
            content["payload"].append(w_msg.id)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    elif request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": ''}
        result = TbWheelmessage.objects.filter(toolt=pk).order_by('m_type', 'w_no').values()
        if result:
            for i in result:
                content["payload"].append(i)
        return Response(content)

    elif request.method == "DELETE":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            data = request.data
            TbWheelmessage.objects.filter(id=data['id']).delete()
            content['payload'].append("OK")
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    elif request.method == "PUT":
        now = datetime.datetime.now()
        content = {"payload": [], "status": 0, "errmsg": ''}
        data = request.data
        try:
            wheel_msg = TbWheelmessage.objects.filter(id=data['id'])
            wheel_msg.update(w_no=data["w_no"],
                             w_type=data["w_type"],
                             w_feed=data["w_feed"],
                             w_speed=data["w_speed"],
                             w_gran=data["w_gran"],
                             m_type=data["m_type"],
                             desi_id=str(request.user), desi_time=now)
            content['payload'].append("OK")
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST"])
def cutter_type(request):
    if request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": ''}
        result = TbCTypeHead.objects.values()
        if result:
            for i in result:
                i["t_type"] = i["c_type"]
                del i["c_type"]
                content["payload"].append(i)
        return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT"])
def desi_size(request, pk):
    if request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            t_type = TbDesignmessage.objects.filter(id=pk).values('t_type')
            if t_type:
                cutter_id = TbCTypeHead.objects.filter(c_type=t_type[0]['t_type']).values('id')
                if cutter_id:
                    result = TbCTypeBody.objects.filter(c_type_id=cutter_id[0]['id']).order_by("desc1").values()
                    for i in result:
                        content["payload"].append(i)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    if request.method == "POST":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            user = request.user
            now = datetime.datetime.now()
            data = request.data["data"]
            result = eval(data)
            many = []
            t_msg = TbDesignmessage.objects.filter(id=pk).values('t_name', 't_ver','t_type')
            t_head = TbCTypeHead.objects.filter(c_type=t_msg[0]["t_type"]).values()
            for i in result:
                if not i["id"]:
                    code = TbCTypeBody.objects.filter(size_name=i["grindingsite"],c_type=t_head[0]['id']).values()
                    many.append(TbDesignsize(t_name=t_msg[0]["t_name"],
                                             t_ver=t_msg[0]["t_ver"],
                                             d_code=code[0]['size_code'],
                                             grindingsite=i["grindingsite"],
                                             g_type=i["g_type"],
                                             g_size=i["g_size"] if i["g_size"] else None,
                                             g_otol=i["g_otol"] if i["g_otol"] else None,
                                             g_itol=i["g_itol"] if i["g_itol"] else None,
                                             desi_id=user,
                                             desi_time=now, toolt_id=pk,desc1=code[0]['desc1']))
                else:
                    sizt_msg = TbDesignsize.objects.filter(id=i['id'])
                    sizt_msg.update(g_size=i["g_size"] if i["g_size"] else None,
                                    g_otol=i["g_otol"] if i["g_otol"] else None,
                                    g_itol=i["g_itol"] if i["g_itol"] else None,
                                    desi_id=str(user), desi_time=now)
            TbDesignsize.objects.bulk_create(many)

        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def biotech(request, pk):
    if request.method == "GET":
        user = request.user
        content = {"payload": {'data': [], 'design_msg': '', 'm_type': []}, "status": 0, "errmsg": ''}
        try:
            t_msg = TbDesignmessage.objects.filter(id=pk).values('t_name', 't_ver')
            wheel_log = TbWheelSort.objects.filter(toolt=pk).values()
            m_type = TbWheelmessage.objects.filter(toolt=pk).values('m_type').distinct()
            if wheel_log:
                for i in wheel_log:
                    content["payload"]['data'].append(i)
            if m_type:
                for j in m_type:
                    content["payload"]['m_type'].append(j['m_type'])
            if t_msg:
                content["payload"]['design_msg'] = {'t_name': t_msg[0]['t_name'], 't_ver': t_msg[0]['t_ver']}
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    elif request.method == "POST":
        content = {"payload": [], "status": 0, "errmsg": ''}
        user = request.user
        try:
            now = datetime.datetime.now()
            data = request.data["data"]
            data = eval(data)
            many = []
            t_msg = TbDesignmessage.objects.filter(id=pk).values('t_name', 't_ver')
            if data and t_msg:
                for i in data:
                    many.append(TbWheelSort(t_name=t_msg[0]["t_name"],
                                            t_ver=t_msg[0]["t_ver"],
                                            m_no=i["m_no"],
                                            w_no=i["w_no"],
                                            w_name=i["w_name"],
                                            w_ceme=i["w_ceme"],
                                            w_bran=i["w_bran"],
                                            w_diam=i["w_diam"],
                                            w_gran=i["w_gran"],
                                            w_arc=i["w_arc"],
                                            w_rnot1=i["w_rnot1"],
                                            m_stat="待上機",
                                            engi_id=user,
                                            status=2,
                                            update_time=now, toolt_id=pk))
                TbWheelSort.objects.bulk_create(many)
            content["payload"] = "OK"
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    elif request.method == "PUT":
        content = {"payload": [], "status": 0, "errmsg": ''}
        data = request.data
        try:
            now = datetime.datetime.now()
            TbWheelSort.objects.filter(id=data["id"]).update(
                w_ceme=data["w_ceme"],
                w_bran=data["w_bran"],
                w_diam=data["w_diam"],
                w_gran=data["w_gran"],
                w_arc=data["w_arc"],
                w_rnot1=data["w_rnot1"],
                update_time=now,
                status=2
            )
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        return Response(content)

    elif request.method == "DELETE":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            data = request.data
            TbWheelSort.objects.filter(id=data['id']).delete()
            content['payload'].append("OK")
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST"])
def productor(request, pk):
    if request.method == "GET":
        content = {"payload": {'data': [], 'page': []}, "status": 0, "errmsg": ''}
        try:
            m_no = request.GET.get("m_no", None)
            status = request.GET.get("status", '4')
            page_num = request.GET.get("page", '')
            id = request.GET.get("id", '')
            # if not m_no:raise Exception(u"你未在此機台進行砂輪修整")
            if not id:
                result = TbProductHead.objects.filter(toolt=pk, m_no=m_no, status=status).values()
            else:
                result = TbProductHead.objects.filter(toolt=pk, m_no=m_no, status=status).values()
            if page_num:
                pag_class = Paginator(result, 20)
            else:
                pag_class = Paginator(result, len(result) + 1)
                page_num = 1
            select_data = pag_class.page(page_num)
            for i in select_data:
                content["payload"]['data'].append(i)
            content["payload"]["page"] = {'count': pag_class.count, 'page': int(page_num),
                                          'page_number': pag_class.page_range[-1]}
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    elif request.method == "POST":
        pass


@api_view(http_method_names=["GET", "POST", "PUT"])
def productor_addit(request, pk):
    if request.method == "GET":
        try:
            now = datetime.datetime.now()
            content = {"payload": {'data': [], 'wheel': [], 'design_msg': ''}, "status": 0, "errmsg": ''}
            m_no = request.GET.get("m_no", None)
            id = request.GET.get("id", "")
            if id and m_no:
                p_msg = TbProductionmessage.objects.filter(head=id).values()
                t_msg = TbProductHead.objects.filter(id=id).values()
                w_wheel = TbWheelSort.objects.filter(m_no=t_msg[0]['m_no'], toolt=pk, status=2).values(
                    'w_no').distinct()
                for t in t_msg:
                    content["payload"]['design_msg'] = t
                for j in w_wheel:
                    content["payload"]['wheel'].append(j)
                if p_msg:
                    for i in p_msg:
                        t_size = TbDesignsize.objects.filter(toolt=pk, d_code=i['d_code']).values('t_name', 'd_code',
                                                                                                  'g_type', 'g_size',
                                                                                                  'g_otol',
                                                                                                  'g_itol', 't_ver',
                                                                                                  'grindingsite', )
                        if t_size:
                            i['grindingsite'] = t_size[0]['grindingsite']
                            if t_size[0]['g_size']:
                                if t_size[0]['g_itol']:
                                    g_itol = t_size[0]['g_size'] + t_size[0]['g_itol']
                                else:
                                    g_itol = t_size[0]['g_size']
                                if t_size[0]['g_otol']:
                                    g_otol = t_size[0]['g_size'] + t_size[0]['g_otol']
                                else:
                                    g_otol = t_size[0]['g_size']
                                i['scope'] = str(round(g_itol, 3)) + '-' + str(round(g_otol, 3))
                            else:
                                i['scope'] = 'NA'
                            content['payload']['data'].append(i)
            if m_no and not id:
                w_wheel = TbWheelSort.objects.filter(m_no=m_no, toolt=pk,
                                                     status=2).values('w_no').distinct()
                t_size = TbDesignsize.objects.filter(toolt=pk).values('t_name', 'd_code', 'g_type', 'g_size', 'g_otol',
                                                                      'g_itol', 't_ver', 'grindingsite', ).order_by('desc1')
                t_msg = TbDesignmessage.objects.filter(id=pk).values('t_name', 't_ver')
                for t in t_msg:
                    content["payload"]['design_msg'] = t
                for j in w_wheel:
                    content["payload"]['wheel'].append(j)
                for i in t_size:
                    if i['g_size']:
                        if i['g_itol']:
                            g_itol = i['g_size'] + i['g_itol']
                        else:
                            g_itol = i['g_size']
                        if i['g_otol']:
                            g_otol = i['g_size'] + i['g_otol']
                        else:
                            g_otol = i['g_size']
                        i['scope'] = str(round(g_itol, 3)) + '-' + str(round(g_otol, 3))
                    else:
                        i['scope'] = 'NA'
                    content['payload']['data'].append(i)
            content['errmsg'] = 0
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    if request.method == "POST":
        try:
            now = datetime.datetime.now()
            content = {"payload": {'data': [], 'wheel': []}, "status": 0, "errmsg": ''}
            data = request.data
            tuer, null, false = 'tuer', 'null', 'false'
            measure = eval(data["measure"])
            data1 = eval(data["data"])
            status = eval(data["status"])
            user = request.user
            many = []
            t_msg = TbDesignmessage.objects.filter(id=pk).values()
            p_heda = TbProductHead()
            p_heda.ct = int(measure['ct'])
            p_heda.me_no = int(measure['me_no'])
            p_heda.t_name = t_msg[0]["t_name"]
            p_heda.t_ver = t_msg[0]["t_ver"]
            p_heda.t_no = measure['t_no']
            p_heda.m_no = measure['m_no']
            p_heda.t_bmta = measure['t_bmta']
            p_heda.t_rnot = measure['t_rnot']
            p_heda.status = status
            p_heda.op_id = user
            p_heda.op_time = now
            p_heda.toolt_id = pk
            p_heda.save()
            for i in data1:
                if not i["id"]:
                    #code = TbCTypeBody.objects.filter(size_name=i["grindingsite"]).values()
                    many.append(TbProductionmessage(t_name=t_msg[0]["t_name"],
                                                    t_ver=t_msg[0]["t_ver"],
                                                    d_code=i['d_code'],
                                                    p_para=i['p_para'],
                                                    wheel_1=i['wheel_1'],
                                                    wheel_2=i['wheel_2'],
                                                    wheel_3=i['wheel_3'],
                                                    me_num=float(i['me_num']) if i['me_num'] else None,
                                                    me_no=int(measure['me_no']),
                                                    t_no=measure['t_no'],
                                                    m_no=measure['m_no'],
                                                    t_bmta=measure['t_bmta'],
                                                    t_rnot=float(measure['t_rnot']),
                                                    status=status,
                                                    op_id=user,
                                                    m_stat="在制",
                                                    op_time=now, head_id=p_heda.id))
                else:
                    sizt_msg = TbProductionmessage.objects.filter(id=i['id'])
                    sizt_msg.update(p_para=i['p_para'], wheel_1=i['wheel_1'], wheel_2=i['wheel_2'],
                                    wheel_3=i['wheel_3'], me_num=float(i['me_num']) if i['me_num'] else None,
                                    me_no=int(measure['me_no']), t_no=measure['t_no'],
                                    m_no=measure['m_no'], t_bmta=measure['t_bmta'], t_rnot=float(measure['t_rnot']),
                                    op_time=now, )
            TbProductionmessage.objects.bulk_create(many)
            # measure = data['measure']
            content['errmsg'] = 0
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    if request.method == "PUT":
        try:
            now = datetime.datetime.now()
            content = {"payload": {'data': [], 'wheel': []}, "status": 0, "errmsg": ''}
            data = request.data
            user = request.user
            measure = eval(data["measure"])
            data1 = eval(data["data"])
            status = eval(data["status"])
            t_msg = TbDesignmessage.objects.filter(id=pk).values()
            p_heda = TbProductHead.objects.filter(id=measure['id'])
            p_heda.update(ct=int(measure['ct']), me_no=int(measure['me_no']), t_no=measure['t_no'],
                          t_bmta=measure['t_bmta'],
                          t_rnot=measure['t_rnot'], op_id=str(user), op_time=now)
            for i in data1:
                code = TbCTypeBody.objects.filter(size_name=i["grindingsite"]).values()
                sizt_msg = TbProductionmessage.objects.filter(id=i['id'])
                sizt_msg.update(p_para=i['p_para'], wheel_1=i['wheel_1'], wheel_2=i['wheel_2'],
                                wheel_3=i['wheel_3'], me_num=float(i['me_num']) if i['me_num'] else None,
                                me_no=int(measure['me_no']), t_no=measure['t_no'],
                                m_no=measure['m_no'], t_bmta=measure['t_bmta'], t_rnot=float(measure['t_rnot']),
                                op_time=now, )
            content['errmsg'] = 0
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST"])
def machine(request):
    if request.method == "GET":
        try:
            content = {"payload": '', "status": 0, "errmsg": ''}
            m_type = request.GET.get('type', '')
            site = str(request.user.site)
            site_map = {'LH': 'SZ', '晉城': 'JC', 'S': 'SZ', '深圳': 'SZ', 'J': 'JC'}
            machine = []
            if m_type:
                type_list = m_type.split("/")
                if len(type_list) > 1:
                    result = TbMachine.objects.all().filter(Q(m_type=type_list[0]) | Q(m_type=type_list[1]),
                                                            site=site_map[site])
                else:
                    result = TbMachine.objects.all().filter(site=site_map[site], m_type=type_list[0])
            else:
                result = TbMachine.objects.all().filter(site=site_map[site])
            for i in result:
                machine.append(i.site + '-' + i.m_type + '-' + ('00' + i.cell_num)[-3:])
            content["payload"] = machine
            content['errmsg'] = 0
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    elif request.method == "POST":
        pass


@api_view(http_method_names=["GET", "POST", "PUT"])
def wheel_no(request, pk):
    if request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            user = request.user
            machine_type = request.GET.get("machine_type", None)
            if not machine_type: raise Exception(u"請傳入機台型號")
            wheel_no = TbWheelmessage.objects.filter(toolt=pk, m_type__icontains=machine_type). \
                values('w_no', 'w_type', 'w_gran').order_by('w_no')
            if wheel_no:
                for i in wheel_no:
                    content["payload"].append(i)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def grind(request, pk):
    if request.method == "GET":
        content = {"payload": {'data': [], 'design_msg': '', 'm_no': []}, "status": 0, "errmsg": ''}
        try:
            grind_msg = TbAblation.objects.filter(toolt=pk).values()
            m_no = TbWheelSort.objects.filter(toolt=pk, status=2).values('m_no').distinct()
            t_msg = TbDesignmessage.objects.filter(id=pk).values('t_name', 't_ver')
            if grind_msg:
                for i in grind_msg:
                    wheel_msg = TbWheelSort.objects.filter(id=i['wsort_id']).values('w_no',
                                                                                    'w_ceme', 'w_diam', 'w_arc',
                                                                                    'w_gran')
                    i['w_no'] = wheel_msg[0]['w_no']
                    i['w_ceme'] = wheel_msg[0]['w_ceme']
                    i['w_diam'] = wheel_msg[0]['w_diam']
                    i['w_arc'] = wheel_msg[0]['w_arc']
                    i['w_gran'] = wheel_msg[0]['w_gran']
                    content["payload"]['data'].append(i)
            if m_no:
                for j in m_no:
                    content["payload"]['m_no'].append(j['m_no'])
            if t_msg:
                content["payload"]['design_msg'] = {'t_name': t_msg[0]['t_name'], 't_ver': t_msg[0]['t_ver']}
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    elif request.method == "POST":
        content = {"payload": [], "status": 0, "errmsg": ''}
        user = request.user
        try:
            data = request.data["data"]
            data = eval(data)
            many = []
            now = datetime.datetime.now()
            t_msg = TbDesignmessage.objects.filter(id=pk).values('t_name', 't_ver')
            if data and t_msg:
                for i in data:
                    b_flag = TbWheelSort.objects.filter(toolt=pk, status=2, m_no=i["m_no"]).values()
                    if b_flag:
                        msg = TbAblation()
                        msg.t_name = t_msg[0]["t_name"]  # 刀具名稱
                        msg.t_ver = t_msg[0]["t_ver"]  # 刀具班次
                        msg.m_no = i["m_no"]  # 機台編號
                        msg.w_bala = i["w_bala"]  #
                        msg.w_speed = i["w_speed"]  # 砂輪轉速
                        msg.w_feed = i["w_feed"]  # 砂輪轉速
                        msg.toolt_id = pk
                        msg.wsort_id = i["id"]
                        msg.m_stat = "在制"
                        msg.engi_id = user
                        msg.update_time = datetime.datetime.now()
                        msg.status = 3  # 狀態
                        msg.save()
                    else:
                        raise Exception(u"你未在此機台進行砂輪修整")
            content["payload"] = {"id": msg.id}
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    elif request.method == "PUT":
        content = {"payload": [], "status": 0, "errmsg": ''}
        data = request.data
        now = datetime.datetime.now()
        try:
            TbAblation.objects.filter(id=data["id"]).update(
                w_bala=data["w_bala"],
                w_speed=data["w_speed"],
                w_feed=data["w_feed"],
                update_time=now,
                status=3
            )
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    elif request.method == "DELETE":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            data = request.data
            TbAblation.objects.filter(id=data['id']).delete()
            content['payload'].append("OK")
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT"])
def grind_wheel_no(request, pk):
    if request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            m_no = request.GET.get("m_no", None)
            if not m_no: raise Exception(u"請傳入機台編號")
            wheel_no = TbWheelSort.objects.filter(toolt=pk, m_no=m_no).values()
            if wheel_no:
                for i in wheel_no:
                    content["payload"].append(i)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT"])
def program(request):
    if request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            m_no = request.GET.get("m_no", None)
            site_num = request.GET.get("sitenum", None)
            if not m_no: raise Exception(u"請傳入機台編號")
            m_type = m_no.split("-")[1]
            modele_code = TbMachineCode.objects.filter(m_type=m_type).values('m_code')
            if modele_code and not site_num:
                result = TbGrindngsite.objects.filter(modelcode=modele_code[0]['m_code']).values()
            if site_num:
                result = TbCorrections.objects.filter(modelcode=modele_code[0]['m_code'], sitenum=site_num).values()
            if result:
                for i in result:
                    content["payload"].append(i)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT"])
def grind_msg(request):
    if request.method == "GET":
        content = {"payload": {'data': [], 'page': []}, "status": 0, "errmsg": ''}
        page_num = request.GET.get("page", '')
        status = request.GET.get('status', '')
        search = request.GET.get('search', None)
        #print(datetime.datetime.now())
        try:
            if status == '4':
                modele_code = TbAblation.objects.filter(status=3).values('m_no', 'toolt_id').distinct()
            else:
                modele_code = TbProductHead.objects.filter(status=4).values('m_no', 'toolt_id').distinct()
            result = []
            #print(len(modele_code))
            #print(datetime.datetime.now())
            for i in modele_code:
                t_msg = TbDesignmessage.objects.filter(id=i['toolt_id']).values()
                #print(t_msg[0])
                #t_msg1 = t_msg[0]
                #t_msg1['m_no']=i['m_no']
                for j in t_msg:
                    j['m_no'] = i['m_no']
                    result.append(j)
                #result.append(t_msg1)
            #print(22222222222222222222222222)
            #print(datetime.datetime.now())
            #print(result)
            if result:
                _result = []
                if search:
                    search = str.lower(search)
                    for i in result:
                        if search in str.lower(i['t_name']) or search in str.lower(i['t_type']):
                            _result.append(i)
                    result = _result
                if page_num:
                    pag_class = Paginator(result, 20)
                else:
                    pag_class = Paginator(result, len(result) + 1)
                    page_num = 1
                select_data = pag_class.page(page_num)
                if select_data:
                    for i in select_data:
                        i["desi_time"] = i["desi_time"].strftime("%Y-%m-%d %H:%M:%S")
                        content["payload"]["data"].append(i)
                content["payload"]["page"] = {'count': pag_class.count, 'page': int(page_num),
                                              'page_number': pag_class.page_range[-1]}
            else:
                raise Exception(u"沒有可以上機的刀具")
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def desi_size_up(request, pk):
    if request.method == "GET":
        content = {"payload": {'data': []}, "status": 0, "errmsg": ''}
        try:
            t_size = TbCTypeBody.objects.filter(c_type=pk).values()
            if t_size:
                for i in t_size:
                    content['payload']['data'].append(i)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    if request.method == "PUT":
        content = {"payload": {'data': []}, "status": 0, "errmsg": ''}
        try:
            data = request.data
            data = eval(data["data"])
            for i in data:
                if i['id']:
                    sizt_msg = TbCTypeBody.objects.filter(id=i['id'])
                    sizt_msg.update(size_name=i["size_name"],
                                    g_type=i['g_type'], status=1 if i['status'] else 0, context=i['context'],
                                    desc1=int(i['desc1']) if i['desc1'] else None)
                else:
                    t_msg = TbCTypeBody()
                    t_msg.c_type_id = pk
                    t_msg.type_part = i['type_part']
                    t_msg.size_name = i['size_name']
                    t_msg.size_code = i['size_code']
                    t_msg.g_type = i['g_type'] if i['g_type'] else None
                    t_msg.status = 1 if i['status'] else 0
                    t_msg.context = i['context'] if i['context'] else None
                    t_msg.desc1 = int(i['desc1']) if i['desc1'] else None
                    t_msg.save()
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)
    if request.method == "POST":
        content = {"payload": {'data': []}, "status": 0, "errmsg": ''}
        try:
            data = request.data
            # data = eval(data["data"])
            t_msg = TbCTypeBody()
            t_msg.c_type_id = pk
            t_msg.type_part = data['type_part']
            t_msg.size_name = data['sizez_name']
            t_msg.size_code = data['size_code']
            t_msg.g_type = data['g_type'] if data['g_type'] else None
            t_msg.status = 1 if data['status'] else 0
            t_msg.context = data['context'] if data['context'] else None
            t_msg.desc1 = int(data['desc1']) if data['desc1'] else None
            t_msg.save()
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)

    if request.method == "DELETE":
        content = {"payload": {'data': []}, "status": 0, "errmsg": ''}
        try:
            data = request.data
            # data = eval(data["data"])
            TbCTypeBody.objects.filter(id=data['id']).delete()
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def save_as(request, pk):
    if request.method == "POST":
        content = {"payload": "", "status": 0, "errmsg": ''}
        try:
            data = request.data
            user = request.user
            now = datetime.datetime.now()
            t_ver = set(TbDesignmessage.objects.filter(t_name=data["t_name"]).values_list("t_ver", flat=1))
            if data["t_ver"] in t_ver:
                content["status"] = 1
                content["errmsg"] = "刀具名稱和版次已存在！"
                return content
            t_msg = TbDesignmessage.objects.filter(id=pk).values('t_spec', 't_type', 't_diam', 't_flen', 't_fnum',
                                                                 't_mmat')
            t_add = TbDesignmessage()
            t_add.t_name = data['t_name']
            t_add.t_ver = data['t_ver']
            t_add.t_spec = t_msg[0]['t_spec']
            t_add.t_type = t_msg[0]['t_type']
            t_add.t_diam = t_msg[0]['t_diam']
            t_add.t_flen = t_msg[0]['t_flen']
            t_add.t_fnum = t_msg[0]['t_fnum']
            t_add.t_mmat = t_msg[0]['t_mmat']
            t_add.desi_id = user
            t_add.desi_time = now
            t_add.save()
            many = []
            many1 = []
            wheel_msg = TbWheelmessage.objects.filter(toolt_id=pk).values()
            for j in wheel_msg:
                many1.append(TbWheelmessage(t_name=t_add.t_name,
                                            t_ver=t_add.t_ver,
                                            w_no=j['w_no'],
                                            w_type=j["w_type"],
                                            w_gran=j["w_gran"],
                                            w_speed=j["w_speed"],
                                            w_feed=j["w_feed"],
                                            m_type=j["m_type"],
                                            desi_id=user,
                                            desi_time=now, toolt_id=t_add.id))
            TbWheelmessage.objects.bulk_create(many1)
            t_size = TbDesignsize.objects.filter(toolt_id=pk).values()
            for i in t_size:
                many.append(TbDesignsize(t_name=t_add.t_name,
                                         t_ver=t_add.t_ver,
                                         d_code=i['d_code'],
                                         grindingsite=i["grindingsite"],
                                         g_type=i["g_type"],
                                         g_size=i["g_size"] if i["g_size"] else None,
                                         g_otol=i["g_otol"] if i["g_otol"] else None,
                                         g_itol=i["g_itol"] if i["g_itol"] else None,
                                         desi_id=user,
                                         desi_time=now, toolt_id=t_add.id))
            TbDesignsize.objects.bulk_create(many)
            content["payload"] = {"id": t_add.id}
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def related_tool(request):
    if request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            t_type = request.GET.get('t_type', None)
            t_diam = request.GET.get('t_diam', None)
            t_fnum = request.GET.get('t_fnum', None)
            t_mmat = request.GET.get('t_mmat', None)
            if not t_diam or not t_type or not t_fnum or not t_mmat:
                raise Exception("請填寫完整")
            t_msg = TbDesignmessage.objects.filter(t_type=t_type, t_diam=t_diam, t_fnum=t_fnum, t_mmat=t_mmat).values()
            if not t_diam or not t_type or not t_fnum or not t_mmat:
                raise Exception("請填寫完整")
            t_msg = TbDesignmessage.objects.filter(t_type=t_type, t_diam=t_diam, t_fnum=t_fnum, t_mmat=t_mmat).values()
            content['payload'] = t_msg
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def copy_wheel(request, pk):
    if request.method == "POST":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            now = datetime.datetime.now()
            data = request.data
            if data:
                w_msg = TbWheelmessage.objects.filter(toolt_id=pk, m_type=data['from']).values()
                if w_msg:
                    for i in w_msg:
                        w_msg = TbWheelmessage()
                        w_msg.t_name = i['t_name']
                        w_msg.t_ver = i['t_ver']
                        w_msg.w_no = i["w_no"]  # 砂輪序號
                        w_msg.w_type = i["w_type"]  # 砂輪型號
                        w_msg.w_feed = i["w_feed"]  # 砂輪粒度
                        w_msg.w_speed = i["w_speed"]  # 砂輪轉速
                        w_msg.w_gran = i["w_gran"]  # 砂輪進給
                        w_msg.m_type = data["to"]  # 機台型號
                        w_msg.toolt_id = pk
                        w_msg.desi_id = request.user
                        w_msg.desi_time = now
                        w_msg.save()
                    content['payload'] = "OK"
                else:
                    content["errmsg"] = "沒有找到需要複製的砂輪"
                    content['payload'] = 1
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def data_nav(request):
    if request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            machine = []
            result={}
            site = request.GET.get('site', '')
            org = request.GET.get('org', '')
            m_type = request.GET.get('m_type','')
            result = TbMachine.objects.values('site').distinct()
            if site and not org:
                result = TbMachine.objects.filter(site=site).values('org').distinct()
            elif site and org and not m_type:
                result = TbMachine.objects.filter(site=site, org=org).values(
                    'm_type').distinct().order_by('m_type')
            elif site and org and m_type:
                result = TbMachine.objects.all().filter(site=site, org=org, m_type=m_type).order_by('new_name')
            if m_type:
                for i in result:
                    result1 = {}
                    result1={'m_no':i.site + '-' + i.m_type + '-' + ('00' + i.cell_num)[-3:]}
                    #machine.append(i.site + '-' + i.m_type + '-' + ('00' + i.cell_num)[-3:])
                    machine.append(result1)
                result = machine
            content['payload'] = result
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def export_data(request):
    if request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            where = []
            sql_where = ''
            pr_sql = "SELECT a.t_name,a.t_ver,e.t_type,a.m_no,d.site,d.org,c.d_code,c.g_size,c.g_otol,\
                      c.g_itol,b.ct,a.me_no,a.t_no,a.t_rnot,a.t_bmta,a.me_num, \
                      a.wheel_1,a.wheel_2,a.wheel_3,a.p_para,a.op_id,a.op_time \
                      FROM `tb_productionmessage` a LEFT JOIN tb_product_head b ON a.head_id = b.id \
                      LEFT JOIN tb_designsize c ON b.toolt_id = c.toolt_id \
                      AND a.d_code = c.d_code LEFT JOIN tb_machine d ON a.m_no=d.new_name  \
                      LEFT JOIN tb_designmessage e ON b.toolt_id=e.id  WHERE a.head_id IS NOT NULL {}"
            site = request.GET.get('site', '')
            org = request.GET.get('org', '')
            t_name = request.GET.get('t_name', '')
            t_type = request.GET.get('t_type', '')
            m_no = request.GET.get('m_no', '')
            m_type = request.GET.get('m_type', '')
            status = request.GET.get('status', '')
            str_time = request.GET.get('str_time', '')
            end_time = request.GET.get('end_time', '')
            op_id = request.GET.get('op_id', '')
            d_code = request.GET.get('size_code', '')
            where.append('1=1')
            if site:
                where.append('d.site="{}"'.format(site))
            if org:
                where.append('d.org="{}"'.format(org))
            if t_name:
                where.append('a.t_name="{}"'.format(t_name))
            if t_type:
                where.append('e.t_type="{}"'.format(t_type))
            if m_no:
                where.append('a.m_no="{}"'.format(m_no))
            if m_type:
                where.append('d.m_type="{}"'.format(m_type))
            if status:
                where.append('a.status="{}"'.format(status))
            if str_time:
                where.append('date(a.op_time)>="{}"'.format(str_time))
            if end_time:
                where.append('date(a.op_time)<="{}"'.format(end_time))
            if op_id:
                where.append('a.op_id="{}"'.format(op_id))
            if d_code:
                where.append('a.d_code="{}"'.format(d_code))
            if where:
                for i in where:
                    sql_where += ' AND {}'.format(i)
            data = conn_ims(pr_sql.format(sql_where))
            data1 = []
            for i in data:
                result = {}
                result['t_name']=i[0]
                result['t_ver'] = i[1]
                result['t_type'] = i[2]
                result['m_no'] = i[3]
                result['site'] = i[4]
                result['org'] = i[5]
                result['d_code'] = i[6]
                result['g_size'] = i[7]
                result['g_otol'] = i[8]
                result['g_itol'] = i[9]
                result['ct'] = i[10]
                result['me_no'] = i[11]
                result['t_no']=i[12]
                result['t_rnot'] = i[13]
                result['t_bmta'] = i[14]
                result['me_num'] = i[15]
                result['wheel_1'] = i[13]
                result['wheel_2'] = i[17]
                result['wheel_3'] = i[18]
                result['p_para'] = i[19]
                result['op_id'] = i[20]
                result['op_time'] = i[21] and i[21].strftime("%Y-%m-%d %H:%M:%S") or i[21]
                data1.append(result)
            data = data1
            content['payload'] = data
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return Response(content)


@api_view(http_method_names=["GET", "POST", "PUT", "DELETE"])
def correct_data(request):
    if request.method == "GET":
        content = {"payload": [], "status": 0, "errmsg": ''}
        try:
            where = []
            header = ['刀具名稱', '版本', '刀具類型','機台編號', '廠區', '單位名稱',
                      '尺寸代碼', '磨削尺寸', '上公差', '下公差', 'CT', '測量序列',
                      '刀具流水號', '裝夾跳動', '刀具基材', '檢測值', '砂輪1補正', '砂輪2補正',
                      '砂輪3補正', '程序補正', '工號', '記錄時間']
            sql_where = ''
            pr_sql = "SELECT a.t_name,a.t_ver,e.t_type,a.m_no,d.site,d.org,c.d_code,c.g_size,c.g_otol,\
                      c.g_itol,b.ct,a.me_no,a.t_no,a.t_rnot,a.t_bmta,a.me_num, \
                      a.wheel_1,a.wheel_2,a.wheel_3,a.p_para,a.op_id,a.op_time \
                      FROM `tb_productionmessage` a LEFT JOIN tb_product_head b ON a.head_id = b.id \
                      LEFT JOIN tb_designsize c ON b.toolt_id = c.toolt_id \
                      AND a.d_code = c.d_code LEFT JOIN tb_machine d ON a.m_no=d.new_name  \
                      LEFT JOIN tb_designmessage e ON b.toolt_id=e.id  WHERE a.head_id IS NOT NULL {}"
            site = request.GET.get('site', '')
            org = request.GET.get('org', '')
            t_name = request.GET.get('t_name', '')
            t_type = request.GET.get('t_type', '')
            m_no = request.GET.get('m_no', '')
            m_type = request.GET.get('m_type', '')
            status = request.GET.get('status', '')
            str_time = request.GET.get('str_time', '')
            end_time = request.GET.get('end_time', '')
            op_id = request.GET.get('op_id', '')
            d_code = request.GET.get('size_code', '')
            where.append('1=1')
            if site:
                where.append('d.site="{}"'.format(site))
            if org:
                where.append('d.org="{}"'.format(org))
            if t_name:
                where.append('a.t_name="{}"'.format(t_name))
            if t_type:
                where.append('e.t_type="{}"'.format(t_type))
            if m_no:
                where.append('a.m_no="{}"'.format(m_no))
            if m_type:
                where.append('d.m_type="{}"'.format(m_type))
            if status:
                where.append('a.status="{}"'.format(status))
            if str_time:
                where.append('date(a.op_time)>="{}"'.format(str_time))
            if end_time:
                where.append('date(a.op_time)<="{}"'.format(end_time))
            if op_id:
                where.append('a.op_id="{}"'.format(op_id))
            if d_code:
                where.append('a.d_code="{}"'.format(d_code))
            if where:
                for i in where:
                    sql_where += ' AND {}'.format(i)
            data = conn_ims(pr_sql.format(sql_where))
            # objs = TbProducttivity.objects.all()
            # 创建 Workbook 时，如果需要写入中文，请使用 utf-8 编码，默认是 unicode 编码。
            wb = xlwt.Workbook(encoding='utf-8')
            ws = wb.add_sheet('補正數據')
            style_k1 = xlwt.easyxf(
                'font: bold on,colour_index black,height 200;borders:left 1,right 1,top 1,bottom 1;pattern: pattern alt_bars, fore_colour yellow, back_colour yellow')
            style_k = xlwt.easyxf(
                'borders:left 1, right 1, top 1, bottom 1; pattern: pattern solid, fore_colour ivory')
            for i in range(0, len(header)):
                ws.col(i).width = 256 * 15  # around 220 pixels
            for rx in range(0, 1):
                for cx in range(0, len(header)):
                    ws.write(rx, cx, header[cx], style_k1)
            excel_row = 1
            for obj in data:
                for i in range(0, len(header)):
                    if i == 21:
                        ws.write(excel_row, i, obj[i] and str(obj[i]) or obj[i], style_k)
                    else:
                        ws.write(excel_row, i, obj[i], style_k)
                excel_row = excel_row + 1
            # ------ 开始：这段代码可以用下面注释段替代，都是本应保存为文件格式的改成保存为数据流，以便返回前端下载
            response = HttpResponse(content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=data.xls'
            wb.save(response)
        except Exception as e:
            content["status"] = 1
            content["errmsg"] = str(e)
        finally:
            return response
